package nc.bs.mmpac.pac0424;

import java.util.ArrayList;
import java.util.List;
import nc.bs.dao.BaseDAO;
import nc.bs.dao.DAOException;
import nc.jdbc.framework.processor.ArrayListProcessor;
import nc.util.mmf.framework.base.MMValueCheck;
import nc.util.mmf.framework.db.MMSqlBuilder;
import nc.util.mmf.framework.db.MMTempTable;
import nc.util.mmf.framework.db.MMTempTableFactory;
import nc.vo.mmpac.pac0424.MaBATempTableVO;
import nc.vo.pub.JavaType;
import nc.vo.pubapp.pattern.exception.ExceptionUtils;













public class MaBABO
{
  private static final String MM_TABLE_NAME_TEMP = "mm_moid_temp";
  private static final String MM_TABLE_NAME_TEMP_WR = "mm_temp_wr";
  private static final String MM_TABLE_NAME_TEMP_PICKM = "mm_temp_pickm";
  
  public String getMaterialSql(String whereSql, String whereSqlforPickmb, String style) {
    String sql = innerJoinTempTable(whereSql, whereSqlforPickmb, style);
    return trim(sql.toString());
  }
  
  private String addtionMaterialSql() {
    MMSqlBuilder sql = new MMSqlBuilder();
    sql.append(" null ");
    sql.append(MaBATempTableVO.VWKID + ", ");
    sql.append(" null ");
    sql.append(MaBATempTableVO.DEPTID + ", ");
    sql.append(" null ");
    sql.append(MaBATempTableVO.VBATCHCODE + ", ");
    sql.append(" null ");
    sql.append(MaBATempTableVO.VBILLCODE + ", ");
    return sql.toString();
  }

  
  public String getMaterialDeptSql(String whereSql, String whereSqlforPickmb, String style) {
    String sql = innerJoinTempTable(whereSql, whereSqlforPickmb, style);
    return trim(sql.toString());
  }
  
  private String addtionMaterialDeptSql() {
    MMSqlBuilder sql = new MMSqlBuilder();
    sql.append(" null ");
    sql.append(MaBATempTableVO.VWKID + ", ");
    sql.append(" mm_mo.cdeptvid ");
    sql.append(MaBATempTableVO.DEPTID + ", ");
    sql.append(" null ");
    sql.append(MaBATempTableVO.VBATCHCODE + ", ");
    sql.append(" null ");
    sql.append(MaBATempTableVO.VBILLCODE + ", ");
    return sql.toString();
  }

  
  public String getMaterialWkSql(String whereSql, String whereSqlforPickmb, String style) {
    String sql = innerJoinTempTable(whereSql, whereSqlforPickmb, style);
    return trim(sql.toString());
  }
  
  private String addtionMaterialWkSql() {
    MMSqlBuilder sql = new MMSqlBuilder();
    sql.append(" mm_mo.cwkid ");
    sql.append(MaBATempTableVO.VWKID + ", ");
    sql.append(" null ");
    sql.append(MaBATempTableVO.DEPTID + ", ");
    sql.append(" null ");
    sql.append(MaBATempTableVO.VBATCHCODE + ", ");
    sql.append(" null ");
    sql.append(MaBATempTableVO.VBILLCODE + ", ");
    return sql.toString();
  }

  
  public String getMaterialBatchCodeSql(String whereSql, String whereSqlforPickmb, String style) {
    String sql = innerJoinTempTable(whereSql, whereSqlforPickmb, style);
    return trim(sql.toString());
  }
  
  private String addtionMaterialBatchCodeSql() {
    MMSqlBuilder sql = new MMSqlBuilder();
    sql.append(" null ");
    sql.append(MaBATempTableVO.VWKID + ", ");
    sql.append(" null ");
    sql.append(MaBATempTableVO.DEPTID + ", ");
    sql.append(" mm_mo.vbatchcode ");
    sql.append(MaBATempTableVO.VBATCHCODE + ", ");
    sql.append(" null ");
    sql.append(MaBATempTableVO.VBILLCODE + ", ");
    return sql.toString();
  }

  
  public String getMaterialBillCodeSql(String whereSql, String whereSqlforPickmb, String style) {
    String sql = innerJoinTempTable(whereSql, whereSqlforPickmb, style);
    return trim(sql.toString());
  }
  
  private String addtionMaterialBillCodeSql() {
    MMSqlBuilder sql = new MMSqlBuilder();
    sql.append(" null ");
    sql.append(MaBATempTableVO.VWKID + ", ");
    sql.append(" null ");
    sql.append(MaBATempTableVO.DEPTID + ", ");
    sql.append(" null ");
    sql.append(MaBATempTableVO.VBATCHCODE + ", ");
    sql.append(" mm_pmo.vbillcode ");
    sql.append(MaBATempTableVO.VBILLCODE + ", ");
    return sql.toString();
  }
  
  private String innerJoinTempTable(String whereSql, String whereSqlforPickmb, String style) {
    MMSqlBuilder sql = new MMSqlBuilder();
    String tempTableName = createTempTable(whereSql);
    String tempwr = getWRTempTableName(tempTableName, tempTableName, style);
    String temppickm = getPickmTempTableName(whereSqlforPickmb, tempTableName, style);
    sql.append("select  ");
    sql.append(getFixSelectSql(tempwr, temppickm));
    sql.append(" from ");
    sql.append(tempwr);
    sql.append("  inner join ");
    sql.append(temppickm);
    sql.append(getFixConditionSelectSql(tempwr, temppickm));
    return sql.toString();
  }






  
  private static String trim(String str) {
    String temp = (str == null) ? "" : str.trim();
    if (temp.startsWith(",")) {
      temp = temp.substring(1, temp.length());
    }
    if (temp.endsWith(",")) {
      temp = temp.substring(0, temp.length() - 1);
    }
    return temp.trim();
  }






  
  private String createTempTable(String whereSql) {
    MMSqlBuilder sql = new MMSqlBuilder();
    sql.append("select cmoid from mm_mo  ");
    sql.append(" left outer join bd_material bd_material_mo ON mm_mo.cmaterialvid = ");
    sql.append(" bd_material_mo.pk_material  ");
    sql.append("  where  mm_mo.fitemstatus  in ('2','3')  and ");
    sql.append(whereSql);
    MMTempTable tempTable = MMTempTableFactory.createTempTable("mm_moid_temp", new String[] { "cmoid" }, new String[] { "CHAR(20)" }, new JavaType[] { JavaType.String
        },



        
        getTempDatas(sql));
    String tableName = tempTable.getRealTableName();
    return tableName.trim();
  }
  
  private String getWRTempTableName(String cmoidTempTableName, String tempTableName, String style) {
    MMSqlBuilder sql = new MMSqlBuilder();
    sql.append("select mm_mo.pk_org pk_org, ");
    sql.append(" mm_mo.cmaterialvid pmaterialvid, ");
    sql.append(" mm_mo.cunitid pcmeasureid, ");
    sql.append(" mm_mo.vfree1 " + MaBATempTableVO.VBFREE1 + ", ");
    sql.append(" mm_mo.vfree2 " + MaBATempTableVO.VBFREE2 + ", ");
    sql.append(" mm_mo.vfree3 " + MaBATempTableVO.VBFREE3 + ", ");
    sql.append(" mm_mo.vfree4 " + MaBATempTableVO.VBFREE4 + ", ");
    sql.append(" mm_mo.vfree5 " + MaBATempTableVO.VBFREE5 + ", ");
    sql.append(" mm_mo.vfree6 " + MaBATempTableVO.VBFREE6 + ", ");
    sql.append(" mm_mo.vfree7 " + MaBATempTableVO.VBFREE7 + ", ");
    sql.append(" mm_mo.vfree8 " + MaBATempTableVO.VBFREE8 + ", ");
    sql.append(" mm_mo.vfree9 " + MaBATempTableVO.VBFREE9 + ", ");
    sql.append(" mm_mo.vfree10 " + MaBATempTableVO.VBFREE10 + ", ");
    if (style.equals("1")) {
      sql.append(addtionMaterialSql());
    }
    else if (style.equals("2")) {
      sql.append(addtionMaterialDeptSql());
    }
    else if (style.equals("3")) {
      sql.append(addtionMaterialWkSql());
    }
    else if (style.equals("4")) {
      sql.append(addtionMaterialBatchCodeSql());
    }
    else if (style.equals("5")) {
      sql.append(addtionMaterialBillCodeSql());
    } 
    sql.append(" sum(isnull(mm_mo.nplanputnum,0)) ");
    sql.append(MaBATempTableVO.NPLANNUM + ", ");
    sql.append(" sum(isnull(wr_b_quality.nwrnum,0))  nwrnum,  ");
    sql.append(" sum(isnull(wr_b_quality.nchecknum,0)) ");
    sql.append(MaBATempTableVO.NCHECKNUM + ", ");
    sql.append("  0 ncalnum ");
    sql.append("from mm_mo mm_mo ");
    sql.append("inner join mm_pmo mm_pmo on mm_pmo.cpmohid = mm_mo.cpmohid ");
    sql.append("left outer join (select mm_wr_product.cbmobid cmobid, ");
    sql.append(" mm_wr_product.cbmaterialvid cbmaterialvid, ");
    sql.append(" sum(isnull(mm_wr_product.nbwrnum, 0)) ");
    sql.append(MaBATempTableVO.NWRNUM + ", ");
    sql.append("sum(case ");
    sql.append(" when mm_wr_quality.fgprocessmethod = 1 then ");
    sql.append("  isnull(mm_wr_quality.ngnum, 0) ");
    sql.append("else ");
    sql.append("  0 ");
    sql.append("end) nchecknum ");
    sql.append("from mm_wr_product mm_wr_product ");
    sql.append("  inner join mm_wr_quality on mm_wr_product.pk_wr_product = ");
    sql.append("   mm_wr_quality.pk_wr_product_q ");
    sql.append("  and mm_wr_quality.dr = 0 ");
    sql.append("  inner join " + tempTableName + " on " + tempTableName + ".cmoid = ");
    sql.append("  mm_wr_product.cbmobid ");
    sql.append("where mm_wr_quality.dr = 0 ");
    sql.append("and mm_wr_product.dr = 0 ");
    sql.append(" and mm_wr_product.fbproducttype = 1 ");
    sql.append("group by mm_wr_product.cbmobid, ");
    sql.append(" mm_wr_product.cbmaterialvid) wr_b_quality on wr_b_quality.cmobid = ");
    sql.append(" mm_mo.cmoid ");
    
    sql.append(" where mm_mo.cmoid in ( ");
    sql.append(" select cmoid from  ");
    sql.append(cmoidTempTableName);
    sql.append(" ) ");
    sql.append(" group by mm_mo.pk_org, ");
    sql.append(" mm_mo.cmaterialvid, ");
    sql.append(" mm_mo.cunitid, ");
    sql.append(" mm_mo.vfree1, ");
    sql.append(" mm_mo.vfree2, ");
    sql.append(" mm_mo.vfree3, ");
    sql.append(" mm_mo.vfree4, ");
    sql.append(" mm_mo.vfree5, ");
    sql.append(" mm_mo.vfree6, ");
    sql.append(" mm_mo.vfree7, ");
    sql.append(" mm_mo.vfree8, ");
    sql.append(" mm_mo.vfree9, ");
    sql.append(" mm_mo.vfree10");
    if (style.equals("2")) {
      sql.append(" , mm_mo.cdeptvid ");
    }
    else if (style.equals("3")) {
      sql.append("  , mm_mo.cwkid ");
    }
    else if (style.equals("4")) {
      sql.append(" , mm_mo.vbatchcode");
    }
    else if (style.equals("5")) {
      sql.append("  , mm_pmo.vbillcode");
    } 
    
    List<List<Object>> data = getTempDatas(sql);
    
    MMTempTable tempTable = MMTempTableFactory.createTempTable("mm_temp_wr", new String[] { 
          "pk_org", "pmaterialvid", "pcmeasureid", "vbfree1", "vbfree2", "vbfree3", "vbfree4", "vbfree5", "vbfree6", "vbfree7", 
          "vbfree8", "vbfree9", "vbfree10", "vwkid", "deptid", "vbatchcode", "vbillcode", "nplannum", "nwrnum", "nchecknum", 
          "ncalnum" }, new String[] { 
          "VARCHAR(20)", "VARCHAR(20)", "VARCHAR(20)", "VARCHAR(20)", "VARCHAR(20)", "VARCHAR(20)", "VARCHAR(20)", "VARCHAR(20)", "VARCHAR(20)", "VARCHAR(20)", 
          "VARCHAR(20)", "VARCHAR(20)", "VARCHAR(20)", "VARCHAR(20)", "VARCHAR(20)", "VARCHAR(40)", "VARCHAR(40)", "Decimal(20,8)", "Decimal(20,8)", "Decimal(20,8)", 
          "Decimal(20,8)" }, new JavaType[] { 
          JavaType.String, JavaType.String, JavaType.String, JavaType.String, JavaType.String, JavaType.String, JavaType.String, JavaType.String, JavaType.String, JavaType.String, 
          JavaType.String, JavaType.String, JavaType.String, JavaType.String, JavaType.String, JavaType.String, JavaType.String, JavaType.BigDecimal, JavaType.BigDecimal, JavaType.BigDecimal, 
          JavaType.BigDecimal }, data);





    
    return tempTable.getRealTableName();
  }

  
  private String getPickmTempTableName(String whereSqlforPickmb, String tempTableName, String style) {
    MMSqlBuilder sql = new MMSqlBuilder();
    sql.append(" select mm_mo.pk_org pk_org, ");
    sql.append(" mm_mo.cmaterialvid pmaterialvid, ");
    sql.append(" pickm_b.cbmaterialvid materialvid, ");
    sql.append(" mm_mo.cunitid pcmeasureid, ");
    sql.append(" pickm_b.cbunitid cbunitid, ");
    sql.append(" mm_mo.vfree1 " + MaBATempTableVO.VBFREE1 + ", ");
    sql.append(" mm_mo.vfree2 " + MaBATempTableVO.VBFREE2 + ", ");
    sql.append(" mm_mo.vfree3 " + MaBATempTableVO.VBFREE3 + ", ");
    sql.append(" mm_mo.vfree4 " + MaBATempTableVO.VBFREE4 + ", ");
    sql.append(" mm_mo.vfree5 " + MaBATempTableVO.VBFREE5 + ", ");
    sql.append(" mm_mo.vfree6 " + MaBATempTableVO.VBFREE6 + ", ");
    sql.append(" mm_mo.vfree7 " + MaBATempTableVO.VBFREE7 + ", ");
    sql.append(" mm_mo.vfree8 " + MaBATempTableVO.VBFREE8 + ", ");
    sql.append(" mm_mo.vfree9 " + MaBATempTableVO.VBFREE9 + ", ");
    sql.append(" mm_mo.vfree10 " + MaBATempTableVO.VBFREE10 + ", ");
    if (style.equals("1")) {
      sql.append(addtionMaterialSql());
    }
    else if (style.equals("2")) {
      sql.append(addtionMaterialDeptSql());
    }
    else if (style.equals("3")) {
      sql.append(addtionMaterialWkSql());
    }
    else if (style.equals("4")) {
      sql.append(addtionMaterialBatchCodeSql());
    }
    else if (style.equals("5")) {
      sql.append(addtionMaterialBillCodeSql());
    } 
    sql.append(" sum(isnull(pickm_b.nquotasnum,0)) ");
    sql.append(MaBATempTableVO.NQUOTASNUM + ", ");
    sql.append(" sum(isnull(pickm_b.");
    sql.append(MaBATempTableVO.NOUTSTOCKNUM + ",0)) ");
    sql.append(MaBATempTableVO.NOUTSTOCKNUM + ", ");
    sql.append(" 0 ncalnum ");
    sql.append(" from mm_mo mm_mo ");
    sql.append(" inner join mm_pmo mm_pmo on mm_pmo.cpmohid = mm_mo.cpmohid  ");
    sql.append(" inner join (select ");
    sql.append(" sum(isnull(mm_pickm_b.nquotnum, 0)) ");
    sql.append(MaBATempTableVO.NQUOTASNUM + ", ");
    sql.append(" sum(isnull(mm_pickm_b.naccoutnum, 0)) ");
    sql.append(MaBATempTableVO.NOUTSTOCKNUM + ", ");
    sql.append(" mm_pickm.csourcebillrowid, ");
    sql.append(" mm_pickm.pk_org, ");
    sql.append(" mm_pickm_b.cbmaterialvid, ");
    sql.append(" mm_pickm_b.cbunitid ");
    sql.append(" from mm_pickm, " + tempTableName + ", mm_pickm_b ");
    sql.append(" where " + tempTableName + ".cmoid = mm_pickm.csourcebillrowid ");
    sql.append(" and mm_pickm_b.cpickmid = mm_pickm.cpickmid ");
    sql.append(" and mm_pickm.dr = 0 ");
    sql.append(" and mm_pickm_b.dr = 0 ");
    if (whereSqlforPickmb != null && whereSqlforPickmb.trim().length() > 0) {
      sql.append(whereSqlforPickmb);
    }
    sql.append(" group by mm_pickm.csourcebillrowid, ");
    sql.append("  mm_pickm.pk_org, ");
    sql.append("  mm_pickm_b.cbmaterialvid, ");
    sql.append(" mm_pickm_b.cbunitid) pickm_b on pickm_b.csourcebillrowid = ");
    sql.append(" mm_mo.cmoid ");
    sql.append(" and pickm_b.pk_org = ");
    sql.append("  mm_mo.pk_org ");
    sql.append(" group by mm_mo.pk_org, ");
    sql.append(" mm_mo.cmaterialvid, ");
    sql.append("  pickm_b.cbmaterialvid, ");
    sql.append("  mm_mo.cunitid, ");
    sql.append(" pickm_b.cbunitid, ");
    sql.append(" mm_mo.vfree1 , ");
    sql.append(" mm_mo.vfree2 , ");
    sql.append(" mm_mo.vfree3 , ");
    sql.append(" mm_mo.vfree4 , ");
    sql.append(" mm_mo.vfree5 , ");
    sql.append(" mm_mo.vfree6 , ");
    sql.append(" mm_mo.vfree7 , ");
    sql.append(" mm_mo.vfree8 , ");
    sql.append(" mm_mo.vfree9, ");
    sql.append(" mm_mo.vfree10 ");
    if (style.equals("2")) {
      sql.append(" , mm_mo.cdeptvid ");
    }
    else if (style.equals("3")) {
      sql.append("  , mm_mo.cwkid ");
    }
    else if (style.equals("4")) {
      sql.append(" , mm_mo.vbatchcode");
    }
    else if (style.equals("5")) {
      sql.append("  , mm_pmo.vbillcode");
    } 
    List<List<Object>> data = getTempDatas(sql);
    
    MMTempTable tempTable = MMTempTableFactory.createTempTable("mm_temp_pickm", new String[] { 
          "pk_org", "pmaterialvid", "materialvid", "pcmeasureid", "cbunitid", "vbfree1", "vbfree2", "vbfree3", "vbfree4", "vbfree5", 
          "vbfree6", "vbfree7", "vbfree8", "vbfree9", "vbfree10", "vwkid", "deptid", "vbatchcode", "vbillcode", "nquotasnum", 
          "noutstocknum", "ncalnum" }, new String[] { 
          "VARCHAR(20)", "VARCHAR(20)", "VARCHAR(20)", "VARCHAR(20)", "VARCHAR(20)", "VARCHAR(20)", "VARCHAR(20)", "VARCHAR(20)", "VARCHAR(20)", "VARCHAR(20)", 
          "VARCHAR(20)", "VARCHAR(20)", "VARCHAR(20)", "VARCHAR(20)", "VARCHAR(20)", "VARCHAR(20)", "VARCHAR(20)", "VARCHAR(40)", "VARCHAR(40)", "Decimal(20,8)", 
          "Decimal(20,8)", "Decimal(20,8)" }, new JavaType[] { 
          JavaType.String, JavaType.String, JavaType.String, JavaType.String, JavaType.String, JavaType.String, JavaType.String, JavaType.String, JavaType.String, JavaType.String, 
          JavaType.String, JavaType.String, JavaType.String, JavaType.String, JavaType.String, JavaType.String, JavaType.String, JavaType.String, JavaType.String, JavaType.BigDecimal, 
          JavaType.BigDecimal, JavaType.BigDecimal }, data);





    
    return tempTable.getRealTableName();
  }



  
  private List<List<Object>> getTempDatas(MMSqlBuilder sql) {
    List<Object> result = null;
    BaseDAO dao = new BaseDAO();
    try {
      result = (List)dao.executeQuery(sql.toString(), new ArrayListProcessor());
    }
    catch (DAOException e) {
      ExceptionUtils.wrappException(e);
    } 
    
    List<List<Object>> data = new ArrayList<List<Object>>();
    if (!MMValueCheck.isEmpty(result)) {
      for (int i = 0; i < result.size(); i++) {
        List<Object> row = new ArrayList<Object>();
        for (int j = 0; j < ((Object[])result.get(i)).length; j++) {
        	row.add(((Object[])result.get(i))[j]); 
        }
        data.add(row);
      } 
    }
    return data;
  }
  
  private String getFixConditionSelectSql(String tempwr, String temppickm) {
    MMSqlBuilder sql = new MMSqlBuilder();
    sql.append(" on " + tempwr + ".pk_org = " + temppickm + ".pk_org ");
    sql.append(" and " + tempwr + ".pmaterialvid = " + temppickm + ".pmaterialvid ");
    sql.append(" and " + tempwr + ".pcmeasureid = " + temppickm + ".pcmeasureid ");
    sql.append(" and isnull(" + tempwr + " .vbfree1, '~')=isnull(" + temppickm + " .vbfree1, '~')");
    sql.append(" and isnull(" + tempwr + " .vbfree2, '~')=isnull(" + temppickm + " .vbfree2, '~')");
    sql.append(" and isnull(" + tempwr + " .vbfree3, '~')=isnull(" + temppickm + " .vbfree3, '~')");
    sql.append(" and isnull(" + tempwr + " .vbfree4, '~')=isnull(" + temppickm + " .vbfree4, '~')");
    sql.append(" and isnull(" + tempwr + " .vbfree5, '~')=isnull(" + temppickm + " .vbfree5, '~')");
    sql.append(" and isnull(" + tempwr + " .vbfree6, '~')=isnull(" + temppickm + " .vbfree6, '~')");
    sql.append(" and isnull(" + tempwr + " .vbfree7, '~')=isnull(" + temppickm + " .vbfree7, '~')");
    sql.append(" and isnull(" + tempwr + " .vbfree8, '~')=isnull(" + temppickm + " .vbfree8, '~')");
    sql.append(" and isnull(" + tempwr + " .vbfree9, '~')=isnull(" + temppickm + " .vbfree9, '~')");
    sql.append(" and isnull(" + tempwr + " .vbfree10, '~')=isnull(" + temppickm + " .vbfree10, '~')");
    sql.append(" and (" + tempwr + ".vwkid = " + temppickm + ".vwkid or (" + tempwr + ".vwkid is null and " + temppickm + ".vwkid is null)) ");
    
    sql.append(" and (" + tempwr + ".deptid = " + temppickm + ".deptid or (" + tempwr + ".deptid is null and " + temppickm + ".deptid is null)) ");
    
    sql.append(" and (" + tempwr + ".vbatchcode = " + temppickm + ".vbatchcode or (" + tempwr + ".vbatchcode is null and " + temppickm + ".vbatchcode is null)) ");
    
    sql.append(" and (" + tempwr + ".vbillcode = " + temppickm + ".vbillcode or (" + tempwr + ".vbillcode is null and " + temppickm + ".vbillcode is null)) ");
    
    return sql.toString();
  }
  
  private String getFixSelectSql(String tempwr, String temppickm) {
    MMSqlBuilder sql = new MMSqlBuilder();
    sql.append(tempwr + " .pk_org, ");
    sql.append(tempwr + " .pmaterialvid, ");
    sql.append(temppickm + ".materialvid, ");
    sql.append(tempwr + " .pcmeasureid, ");
    sql.append(temppickm + ".cbunitid, ");
    sql.append(tempwr + " .vbfree1, ");
    sql.append(tempwr + " .vbfree2, ");
    sql.append(tempwr + " .vbfree3, ");
    sql.append(tempwr + ".vbfree4, ");
    sql.append(tempwr + ".vbfree5, ");
    sql.append(tempwr + ".vbfree6, ");
    sql.append(tempwr + ".vbfree7, ");
    sql.append(tempwr + ".vbfree8, ");
    sql.append(tempwr + ".vbfree9, ");
    sql.append(tempwr + " .vbfree10, ");
    sql.append(tempwr + ".vwkid, ");
    sql.append(tempwr + ".deptid, ");
    sql.append(tempwr + ".vbatchcode, ");
    sql.append(tempwr + ".vbillcode, ");
    sql.append(tempwr + ".nplannum, ");
    sql.append(temppickm + ".nquotasnum, ");
    sql.append(tempwr + ".nwrnum, ");
    sql.append(tempwr + ".nchecknum, ");
    sql.append(temppickm + ".noutstocknum, ");
    sql.append("  0 ncalnum, ");
    sql.append("  clck.clcinventoryid, ");
    sql.append("  clck.clnprice, ");
    sql.append("  clck.clcaccountperiod ");
    return sql.toString();
  }
}
